import json
from datetime import datetime

import requests
from urllib.parse import urljoin

import pymysql

BASE_URL = 'http://cmp.msa.gov.cn/cbdj_mgt/shipname/queryPrint.action?act=query&shipNameQO.apply_date_start=2024-10-01&shipNameQO.apply_date_end=2024-12-31&page=1&rows=10'
headers = {
    'Accept': 'application/json, text/javascript, */*; q=0.01',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/json;charset=utf-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': '8owauR5ZLxXH9sWWPQDsUpH9bAtNgLCm9i3AetnUhRxY7KsSbdLEIagOWxLdWE6rpxJXxSgcoaFerR9Kk4CnPv1jirf6L2Do%2BoVLwmAWX0biUmAgLXJNPKFqOweBVef9uZ56CFAfMHqm2GSlNeI8ECOWll8lAWLJ3Qy3v%2BqgG6hfXi0B%2Fc2y%2BTcrLIG97XykWRP%2BV1i0nKuw0rIxqzoAb1sSScwpK3RvIpuXEeVAujYKTPzfflgnMD3Ns%2F8cdNzrZOzFospsjjq0hDpuEMkbvN2UbD%2FRNnlh2mI6m6DWMliVAqFpj7QgD%2BLwI14HHTBFxNvK6ZCK%2BsJGBNh5syhuBiHJ%2BQhNQr9sA7eS9WA202dPqZwDXOysWd98zxXwoi%2Bbwd%2B6KA169BSBwc0zVmNqPnLwZ240T13fYe7kruRcGyrc2DRWl1LYOdek%2F%2FtF%2F7d1Ss%2F3LI3ZSSNFKzQCaLLXwlQ51qe5b0NE4Gu8TkhjyCY%3D',

    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': '53ETW8y98k1P-Ic2yMAV7LyHod5i4xrEnzB1WsTcDeCLUL0II2Ah!-1812561480!1739786538173'
}

session = requests.Session()
response_lo = session.post(BASE_URL, headers=headers, cookies=cookies)
strData = json.loads(response_lo.text)

if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'xt_register_reserved_ship_name'
    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())
                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))
                # print("!!!"+fields)
                # print("~~~"+values)

                # value值是json的字段无法直接存入库，要转成string
                json_apply_date = record['apply_date']
                json_create_time = record['create_time']
                json_expiry_date = record['expiry_date']
                json_operate_time = record['operate_time']

                # json对象转成string后重新赋值回原来的字段
                str_apply_date = json.dumps(json_apply_date)
                str_create_time = json.dumps(json_create_time)
                str_expiry_date = json.dumps(json_expiry_date)
                str_operate_time = json.dumps(json_operate_time)

                record['apply_date'] = str_apply_date
                record['create_time'] = str_create_time
                record['expiry_date'] = str_expiry_date
                record['operate_time'] = str_operate_time

                names = record['apply_date']
                print(names)
                #print(tuple(record.keys()))
                # 构建插入语句
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))
                # print("%%%" + insert_stmt)
                # print(">>>" + str(record.values()))
        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()